﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// DBConn 的摘要说明
/// </summary>
public class DBConn
{
    private SqlConnection conn;
    public DBConn()
    {
        conn = new SqlConnection(ConfigurationManager.AppSettings["connectString"]);
        conn.Open();
    }

    public DBConn(string ConnStr)
    {
        conn = new SqlConnection(ConnStr);
        conn.Open();
    }

    public void Close()
    {
        if (conn.State == ConnectionState.Open) {
            conn.Close();
            conn = null;
        }
        
    }

    public SqlDataReader getDataReader(string SQLQuery)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(SQLQuery, conn);
            return cmd.ExecuteReader();
        }
        catch
        {
            Exception e = new Exception("执行sql时出错:" + SQLQuery);
            throw e;
        }

    }

    public int Insert(string SQLQuery)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(SQLQuery, conn);
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            Exception e = new Exception("执行sql时出错:" + SQLQuery);
            throw e;
        }
    }

    public int Update(string SQLQuery)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(SQLQuery, conn);
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            Exception e = new Exception("执行sql时出错:" + SQLQuery);
            throw e;
        }
    }

    public int Delete(string SQLQuery)
    {
        SqlCommand cmd = new SqlCommand(SQLQuery, conn);
        return cmd.ExecuteNonQuery();
    }

    public DataSet getDataSet(string SQLQuery)
    {
        SqlDataAdapter da = new SqlDataAdapter(SQLQuery, conn);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
            return ds;
        }
        catch
        {
            Exception e = new Exception("执行sql时出错:" + SQLQuery);
            throw e;
        }
        finally
        {
            da.Dispose();
            da = null;
        }

    }

    public bool DoTransaction(string sql)
    {
        bool successState = false;

        SqlTransaction myTrans = conn.BeginTransaction();
        SqlCommand command = new SqlCommand(sql, conn, myTrans);
        try
        {
            command.ExecuteNonQuery();
            myTrans.Commit();
            successState = true;
        }
        catch
        {
            myTrans.Rollback();
            Exception e = new Exception("执行sql时出错:"　+ sql);
            throw e;
        }

        return successState;
    }


    public ICollection CreateSource(string SQLQuery)
    {

        SqlDataAdapter da = new SqlDataAdapter(SQLQuery, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "mytable");
        da.Dispose();
        da = null;
        return ds.Tables["mytable"].DefaultView;

    }

    public ICollection CreateSource(string SQLQuery, string SortField)
    {
        SqlDataAdapter da = new SqlDataAdapter(SQLQuery, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "mytable");
        DataView dv = ds.Tables["mytable"].DefaultView;
        dv.Sort = SortField;
        da.Dispose();
        da = null;
        return dv;
    }


}
